﻿using Microsoft.Data.SqlClient;
using System;
using System.Data;

namespace IceCS.EMS.DAL
{
    public static class DBHelper
    {
        /// <summary>
        /// 定义数据库连接字符串
        /// </summary>
        public static string ConnectionString { get; set; }

        static DBHelper()
        {
            ConnectionString = "Server=localhost;Database=emp;User ID=sa;Password=qweRTY123$;Trusted_Connection=False";
        }

        public static SqlConnection GetConnection()
        {
            var conn = new SqlConnection(ConnectionString);
            conn.Open();
            return conn;
        }
        /// <summary>
        /// 获取数据的方法，返回DataTable对象，参数为一个select语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {

            using (var connection = GetConnection())
            {
                try
                {
                    var cmd = new SqlCommand(sql, connection);
                    var adapter = new SqlDataAdapter(cmd);
                    var table = new DataTable();
                    adapter.Fill(table);
                    return table;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 获取数据的重载方法，返回DataTable对象，参数为一个参数化的select语句和参数对象数组
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, SqlParameter[] param)
        {
            using (var connection = GetConnection())
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    cmd.Parameters.AddRange(param);
                    var dapt = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    dapt.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }


        /// <summary>
        /// 获取单个结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleData(string sql)
        {
            using (var connection = GetConnection())
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    return cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 执行更新的方法，返回一个布尔值，参数为一个insert|update|delete语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool ExcuteCommand(string sql)
        {

            using (var connection = GetConnection())
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 执行更新的重载方法，返回一个int值，参数为一个参数化的insert|update|delete语句和参数对象数组
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExcuteCommand(string sql, SqlParameter[] param)
        {
            using (var connection = GetConnection())
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    cmd.Parameters.AddRange(param);
                    int affectCount = cmd.ExecuteNonQuery();
                    return affectCount;
                }
                catch(SqlException ex)
                {
                    return -1;
                }
                catch (Exception ex)
                {
                    return -1;
                }
            }
        }
    }
}
